from jobs.sql_mehods import *
import pandas as pd
from datetime import datetime
db = MySQLHelper(host='mmservice-05.mysql.hotgrid.cn', port=3306,
                 user='electricity_api_service', password='GJlfh7&#jg',
                 db='electricity_data', charset='utf8')



# 获取全部的产治污设备的信息表
def  get_all_yield_treat():
    sql=f"""select guid from elecdata_basic_info ebi 
inner JOIN
(
SELECT id 
FROM elec_dev_info
WHERE dev_type_name LIKE '%产污设施%' OR dev_type_name LIKE '%治污设施%'
) t1 on t1.id = ebi.devid 
where data_type_id = '8'"""
    data = pd.DataFrame(db.execute_select(sql))
    return data

# 获取某段时间的产治污的信息
def get_time_data(time_front,time_end):
    data_object = datetime.strptime(time_front, "%Y-%m-%d %H:%M:%S")
    data_year = data_object.year
    data_month = "{:02d}".format(data_object.month)
    sql = f"""

SELECT 
table1.ent_id,table1.ent_name,table1.industry_type,table1.industry_type_id,
table1.county_id,table1.county_name,table1.town_id,table1.town_name,
 table1.dev_type,  table1.dev_type_name,table1.treat_dev_id,table1.yield_dev_id,table1.devid,
    table1.treat_dev_name AS treat_dev_name_1,
    table1.yield_dev_name AS yield_dev_name_1,
    round((table2.value - table1.value),2) as value,
		 CASE table1.dev_type WHEN 3 THEN  round(table1.rated_power * 0.1,2) ELSE NULL END as treat_power_value,
    CASE table1.dev_type WHEN 2 THEN round(table1.rated_power * 0.2,2) ELSE NULL END as yield_power_value
FROM
    (SELECT DISTINCT ebi.guid as guid ,edi.id as devid,dit.industry_type,ei.industry_type_id,
		ei.county_id,ei.county_name,ei.town_id,ei.town_name,edi.dev_type,edi.dev_type_name,
       e5m.value, edi.rated_power,edi.ent_id,ei.ent_name,ryt.treat_dev_id,ryt.yield_dev_id,
       IF(ryt.treat_dev_id = edi.id, edi.dev_name, NULL) AS treat_dev_name,
       IF(ryt.yield_dev_id = edi.id, edi.dev_name, NULL) AS yield_dev_name
    FROM elecdata_basic_info ebi
    LEFT JOIN elecdata_data_5m_{data_year}{data_month}  e5m  on ebi.guid = e5m.guid 
    LEFT JOIN elec_dev_info edi ON ebi.devid = edi.id
    LEFT JOIN elec_enterprise_info ei ON edi.ent_id = ei.id
    LEFT JOIN rel_yield_treat ryt ON ryt.ent_id = ei.id
		left join dict_industry_type dit on dit.id = ei.industry_type_id
    WHERE  ebi.data_type_id = 8
    AND e5m.data_time = '{time_front}' 
    AND NOT (IF(ryt.treat_dev_id = edi.id, edi.dev_name, NULL) IS NULL AND IF(ryt.yield_dev_id = edi.id, edi.dev_name, NULL) IS NULL) 
		GROUP BY ebi.guid
    ) as table1
JOIN
    (SELECT DISTINCT ebi.guid as guid ,edi.id as devid,dit.industry_type,ei.industry_type_id,
ei.county_id,ei.county_name,ei.town_id,ei.town_name,edi.dev_type,edi.dev_type_name,
       e5m.value,  edi.rated_power,edi.ent_id,ei.ent_name,ryt.treat_dev_id,ryt.yield_dev_id,
       IF(ryt.treat_dev_id = edi.id, edi.dev_name, NULL) AS treat_dev_name,
       IF(ryt.yield_dev_id = edi.id, edi.dev_name, NULL) AS yield_dev_name
    FROM elecdata_basic_info ebi
    LEFT JOIN elecdata_data_5m_202401  e5m  on ebi.guid = e5m.guid 
    LEFT JOIN elec_dev_info edi ON ebi.devid = edi.id
    LEFT JOIN elec_enterprise_info ei ON edi.ent_id = ei.id
    LEFT JOIN rel_yield_treat ryt ON ryt.ent_id = ei.id
			left join dict_industry_type dit on dit.id = ei.industry_type_id
    WHERE  ebi.data_type_id = 8
    AND e5m.data_time = '{time_end}' 
    AND NOT (IF(ryt.treat_dev_id = edi.id, edi.dev_name, NULL) IS NULL AND IF(ryt.yield_dev_id = edi.id, edi.dev_name, NULL) IS NULL)
		GROUP BY ebi.guid
    ) as table2
ON table1.guid = table2.guid AND table1.devid = table2.devid;"""
    data=pd.DataFrame(db.execute_select(sql))
    return data

# 将无数据的以0值处理并加入到数据表中
def convert_warn_original_data(emptime):
    # 获得所有的产 治污  设备的guid
    all_yield_treat=get_all_yield_treat()
    # 获得 具有记录的该时刻的产治污 数据
    original_yeild_treat_data=get_time_data(emptime)
    # 使得all_yield_treat中guid 在original_yeild_treat_data中不存在的
    return 1


if __name__ == "__main__":
    start = "2024-01-26 10:00:00"
    end = "2024-01-26 10:05:00"
    df = get_time_data(start,end)
    #table1.devid为当前设备对应的id  根据这份数据  绑定的产污  治污id 的关系
    # 假设你的数据是df

    # 假设你的数据是df
    df['warn'] = False  # 初始化警告列

    # 创建掩码来找到满足你给出的条件的行
    mask_treat = (df['treat_dev_id'] == df['devid']) & (df['value'] < df['treat_power_value'])
    mask_yield = (df['yield_dev_id'] == df['devid']) & (df['value'] > df['yield_power_value'])

    # 使用这些掩码来将满足条件的行的 warn 列设置为 True
    df.loc[mask_treat | mask_yield, 'warn'] = True

    # 使用 isin 函数找到 df['devid'] 中与 df['treat_dev_id'] 相同的项
    mask1 = df['devid'].isin(df['treat_dev_id'])

    # 筛选出相匹配的行
    df1 = df[mask1]
    df1=df1[df1['warn']==True]
    df1.to_csv("治污.csv")

    mask2 = df['devid'].isin(df['yield_dev_id'])
    # 筛选出相匹配的行
    df2 = df[mask2]
    df2=df2[df2['warn']==True]
    df2.to_csv("产污.csv")


